﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-9-9
Description:tongxunlu三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class TongxunluService
	{
		#region 增加一条数据
		public static int AddTongxunlu(Tongxunlu tongxunlu)
		{
			string sql =
			"INSERT INTO tongxunlu(uname,tel,email,address,zhiwu,beizhu) "+
			"VALUES (@uname,@tel,@email,@address,@zhiwu,@beizhu)";
			
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
						
					new SQLiteParameter("@uname",tongxunlu.Uname),
						
					new SQLiteParameter("@tel",tongxunlu.Tel),
						
					new SQLiteParameter("@email",tongxunlu.Email),
						
					new SQLiteParameter("@address",tongxunlu.Address),
						
					new SQLiteParameter("@zhiwu",tongxunlu.Zhiwu),
						
					new SQLiteParameter("@beizhu",tongxunlu.Beizhu)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteTongxunluById(int id)	
		{
			string sql="DELETE From  tongxunlu  Where id=@id";
			 SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public static int ModifyTongxunluByid(Tongxunlu tongxunlu)
		{
		string sql ="UPDATE "+"tongxunlu"+" set "+
					"uname=@uname, "+
					"tel=@tel, "+
					"email=@email, "+
					"address=@address, "+
					"zhiwu=@zhiwu, "+
					"beizhu=@beizhu "+ 
				" Where id=@id";
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id",tongxunlu.Id),
					new SQLiteParameter("@uname",tongxunlu.Uname),
					new SQLiteParameter("@tel",tongxunlu.Tel),
					new SQLiteParameter("@email",tongxunlu.Email),
					new SQLiteParameter("@address",tongxunlu.Address),
					new SQLiteParameter("@zhiwu",tongxunlu.Zhiwu),
					new SQLiteParameter("@beizhu",tongxunlu.Beizhu)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}					
		#endregion
		#region 查询数据
		public static IList<Tongxunlu> GetAllTongxunlus()
		{
			string sqlAll = "SELECT * FROM tongxunlu";
			return GetTongxunlusBySql(sqlAll);
		}
		//以标识列获得对象
		public static Tongxunlu GetTongxunluByid(object id)
		{
			string sql = "SELECT * FROM "+"tongxunlu"+" WHERE id = @id";
			
            using (SQLiteDataReader reader = DBHelper.GetReader(sql, new SQLiteParameter("@id", id)))
			{
				 
				if (reader.Read())
				{
					Tongxunlu tongxunlu = new Tongxunlu();
					tongxunlu.Id=Convert.ToInt32(reader["id"]);
					tongxunlu.Uname=Convert.ToString(reader["uname"]);
					tongxunlu.Tel=Convert.ToString(reader["tel"]);
					tongxunlu.Email=Convert.ToString(reader["email"]);
					tongxunlu.Address=Convert.ToString(reader["address"]);
					tongxunlu.Zhiwu=Convert.ToString(reader["zhiwu"]);
					tongxunlu.Beizhu=Convert.ToString(reader["beizhu"]);
					reader.Close();
					
					return tongxunlu;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			 
		}
					
		//私有方法供构建好的SQL语句调用
		private static IList<Tongxunlu> GetTongxunlusBySql(string safeSql)
		{
			List<Tongxunlu> list = new List<Tongxunlu>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tongxunlu tongxunlu = new Tongxunlu();
					tongxunlu.Id=Convert.ToInt32(row["id"]);
					tongxunlu.Uname=Convert.ToString(row["uname"]);
					tongxunlu.Tel=Convert.ToString(row["tel"]);
					tongxunlu.Email=Convert.ToString(row["email"]);
					tongxunlu.Address=Convert.ToString(row["address"]);
					tongxunlu.Zhiwu=Convert.ToString(row["zhiwu"]);
					tongxunlu.Beizhu=Convert.ToString(row["beizhu"]);
					list.Add(tongxunlu);
				}
				return list;
			}
             
		}
		private static IList<Tongxunlu> GetTongxunlusBySql( string sql, params SQLiteParameter[] values )
		{
			List<Tongxunlu> list = new List<Tongxunlu>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					Tongxunlu tongxunlu = new Tongxunlu();
					tongxunlu.Id=Convert.ToInt32(row["id"]);
					tongxunlu.Uname=Convert.ToString(row["uname"]);
					tongxunlu.Tel=Convert.ToString(row["tel"]);
					tongxunlu.Email=Convert.ToString(row["email"]);
					tongxunlu.Address=Convert.ToString(row["address"]);
					tongxunlu.Zhiwu=Convert.ToString(row["zhiwu"]);
					tongxunlu.Beizhu=Convert.ToString(row["beizhu"]);
					list.Add(tongxunlu);
				}
				return list;
			}
            
			 
		}
		#endregion
		 
	}
}

